﻿// DataProviders/SqlServerProvider.cs

using System.Collections.Generic;
using System.Linq;
using SqlSugar;
using DatabaseManager.Core.Models;

namespace DatabaseManager.DataProviders
{
    public class SqlServerProvider : IDataProvider
    {
        private readonly ISqlSugarClient _db;

        public SqlServerProvider(ISqlSugarClient db)
        {
            _db = db;
        }

        public bool TestConnection() => _db.Ado.GetScalar("SELECT 1") != null;

        public List<TableInfo> GetTables()
        {
            var sql = @"
                SELECT 
                    t.name AS Name,
                    s.name AS Schema,
                    ep.value AS Description
                FROM sys.tables t
                INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                LEFT JOIN sys.extended_properties ep ON ep.major_id = t.object_id AND ep.name = 'MS_Description'
                ORDER BY t.name";

            return _db.Ado.SqlQuery<TableInfo>(sql);
        }

        public List<ColumnInfo> GetColumns(string tableName)
        {
            var sql = $@"
                SELECT 
                    c.name AS Name,
                    ty.name AS DataType,
                    c.is_nullable AS IsNullable,
                    def.definition AS DefaultValue,
                    ep.value AS Description,
                    c.column_id AS OrdinalPosition,
                    CASE WHEN ic.index_column_id IS NULL THEN 0 ELSE 1 END AS IsPrimaryKey
                FROM sys.columns c
                INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
                LEFT JOIN sys.default_constraints def ON def.parent_object_id = c.object_id AND def.parent_column_id = c.column_id
                LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
                LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id AND ic.is_included_column = 0
                INNER JOIN sys.tables t ON c.object_id = t.object_id
                WHERE t.name = '{tableName}'
                ORDER BY c.column_id";

            return _db.Ado.SqlQuery<ColumnInfo>(sql);
        }

        public List<string> GetDatabases()
        {
            var sql = "SELECT name FROM master.sys.databases";
            return _db.Ado.SqlQuery<string>(sql).ToList();
        }
    }
}